﻿Imports System.Data.SqlClient

Public Class IndicadoresADO
    ReadOnly MiConexion As New ConexionADO()
    ReadOnly cnx As New SqlConnection(MiConexion.GetCnx)
    ReadOnly cmd As New SqlCommand()

    Public Function pr_Get_Mes(ByVal sTipoIndicador As String) As DataTable
        Dim dt As New DataTable
        Try
            cmd.Connection = cnx
            cmd.CommandType = CommandType.Text
            cmd.CommandText = String.Format("SELECT DISTINCT DesMesId FROM dbo.V_Indicador WHERE CodTipoIndicador='{0}' ORDER BY DesMesId", sTipoIndicador)

            Using ada As New SqlDataAdapter(cmd)
                ada.Fill(dt)
            End Using
            Return dt
        Catch ex As Exception
            MsgBox("IndicadoresADO (pr_Get_Mes) --> " & ex.Message, MsgBoxStyle.Critical)
            Return Nothing
        End Try
    End Function

    Public Function pr_Get_Periodo(ByVal sTipoIndicador As String) As DataTable
        Dim dt As New DataTable
        Try
            cmd.Connection = cnx
            cmd.CommandType = CommandType.Text
            cmd.CommandText = String.Format("SELECT DISTINCT NumAnioId FROM dbo.V_Indicador WHERE CodTipoIndicador='{0}' ORDER BY NumAnioId DESC", sTipoIndicador)

            Using ada As New SqlDataAdapter(cmd)
                ada.Fill(dt)
            End Using
            Return dt
        Catch ex As Exception
            MsgBox("IndicadoresADO (pr_Get_Periodo) --> " & ex.Message, MsgBoxStyle.Critical)
            Return Nothing
        End Try
    End Function

    Public Function pr_Get_Ruta(ByVal sTipoIndicador As String) As DataTable
        Dim dt As New DataTable
        Try
            cmd.Connection = cnx
            cmd.CommandType = CommandType.Text
            cmd.CommandText = String.Format("SELECT DISTINCT DesRutaId FROM dbo.V_Indicador WHERE CodTipoIndicador = '{0}' ORDER BY DesRutaId", sTipoIndicador)

            Using ada As New SqlDataAdapter(cmd)
                ada.Fill(dt)
            End Using
            Return dt
        Catch ex As Exception
            MsgBox("IndicadoresADO (pr_Get_Ruta) --> " & ex.Message, MsgBoxStyle.Critical)
            Return Nothing
        End Try
    End Function

    Public Function pr_Get_TipoPasajero(ByVal sTipoIndicador As String) As DataTable
        Dim dt As New DataTable
        Try
            cmd.Connection = cnx
            cmd.CommandType = CommandType.Text
            cmd.CommandText = String.Format("SELECT DISTINCT DesPasajeroId FROM dbo.V_Indicador WHERE CodTipoIndicador='{0}' ORDER BY DesPasajeroId", sTipoIndicador)

            Using ada As New SqlDataAdapter(cmd)
                ada.Fill(dt)
            End Using
            Return dt
        Catch ex As Exception
            MsgBox("IndicadoresADO (pr_Get_TipoPasajero) --> " & ex.Message, MsgBoxStyle.Critical)
            Return Nothing
        End Try
    End Function

    Public Function pr_CargarIndicadorPersonal(ByVal sFiltro As String, ByVal nOpcion As Integer, ByVal sCanReg As String, ByVal lTodo As Boolean) As DataTable
        Dim sql As String = ""
        ' selecciono que grafico se quiere cargar
        Select nOpcion
            Case 1
                sql = String.Format("SELECT d.DesSerieId, d.DesArgumentId, SUM(d.canIndicador) as canIndicador FROM (SELECT {0} AS DesSerieId,DesLugarResidenciaId as DesArgumentId,canIndicador FROM DBO.V_Indicador WHERE (CodTipoIndicador='01' AND {1})) d GROUP BY d.DesSerieId,d.DesArgumentId ORDER BY d.DesSerieId, d.DesArgumentId", IIf(lTodo, "'TODO'", "DesMesId"), sFiltro)
            Case 2
                sql = String.Format("SELECT d.DesSerieId, d.DesArgumentId, SUM(d.canIndicador) as canIndicador FROM (SELECT {0} AS DesSerieId,DesLugarResidenciaId as DesArgumentId,canIndicador FROM DBO.V_Indicador WHERE (CodTipoIndicador='01' AND {1})) d GROUP BY d.DesSerieId,d.DesArgumentId ORDER BY d.DesSerieId, d.DesArgumentId", IIf(lTodo, "'TODO'", "DesRutaId"), sFiltro)
            Case 3
                sql = String.Format("SELECT d.DesSerieId, d.DesArgumentId, SUM(d.canIndicador) as canIndicador FROM (SELECT {0} AS DesSerieId,DesMesId as DesArgumentId,canIndicador FROM DBO.V_Indicador WHERE (CodTipoIndicador='01' AND {1})) d GROUP BY d.DesSerieId,d.DesArgumentId ORDER BY d.DesSerieId, d.DesArgumentId", IIf(lTodo, "'TODO'", "NumAnioId"), sFiltro)
            Case 4
                sql = String.Format("SELECT TOP {0} DesPasajeroId,count(CanIndicador) as CanIndicador FROM DBO.V_Indicador WHERE (CodTipoIndicador='01') AND {1} GROUP BY DesPasajeroId ORDER BY CanIndicador DESC", sCanReg, sFiltro)
            Case 5
                sql = String.Format("SELECT TOP {0} DesPasajeroId,count(CanIndicador) as CanIndicador FROM DBO.V_Indicador WHERE (CodTipoIndicador='01') AND {1} GROUP BY DesPasajeroId ORDER BY CanIndicador", sCanReg, sFiltro)
            Case 6
                sql = String.Format("select vdet.DesPasajeroId,vdet.CanIndicador FROM (SELECT vind.DesPasajeroId,COUNT(*) as CanIndicador FROM (SELECT '01' as codTipoIndicador,CAST(YEAR(cm.fchviaje) as char) as NumAnioId,me.desmes as DesMesId,dm.NomPasajero+' '+dm.AppPasajero+' '+dm.ApmPasajero as DesPasajeroId,rtrim(ls.NomLugar)+'-'+la.NomLugar AS DesRutaId FROM dbo.d_manifiesto dm inner join dbo.c_manifiesto cm on cm.codviaje=dm.codviaje inner join (SELECT CAST(right(anomes,2) as int) as nromes,desmes FROM dbo.meses) me on me.nromes=MONTH(cm.fchviaje) inner join dbo.m_lugar ls on ls.codLugar=cm.codLugarOrigen inner join dbo.m_lugar la on la.codLugar=cm.codLugarDestino WHERE cm.estadomanifiesto>='2' AND dm.FlgChecking=0 AND dm.TipPorExcepcion<>'3') vind WHERE {0}GROUP BY vind.DesPasajeroId) vdet order by vdet.CanIndicador DESC", sFiltro)
            Case 7
                sql = String.Format("select vdet.DesPasajeroId,vdet.CanIndicador FROM (SELECT vind.DesPasajeroId,COUNT(*) as CanIndicador FROM (SELECT '01' as codTipoIndicador,CAST(YEAR(cm.fchviaje) as char) as NumAnioId,me.desmes as DesMesId,dm.NomPasajero+' '+dm.AppPasajero+' '+dm.ApmPasajero as DesPasajeroId,rtrim(ls.NomLugar)+'-'+la.NomLugar AS DesRutaId FROM dbo.d_manifiesto dm inner join dbo.c_manifiesto cm on cm.codviaje=dm.codviaje inner join (SELECT CAST(right(anomes,2) as int) as nromes,desmes FROM dbo.meses) me on me.nromes=MONTH(cm.fchviaje) inner join dbo.m_lugar ls on ls.codLugar=cm.codLugarOrigen inner join dbo.m_lugar la on la.codLugar=cm.codLugarDestino WHERE cm.estadomanifiesto>='2' AND dm.FlgChecking=0 AND dm.TipPorExcepcion='3') vind WHERE {0}GROUP BY vind.DesPasajeroId) vdet order by vdet.CanIndicador DESC", sFiltro)
        End Select
        Using cn As New SqlConnection(MiConexion.GetCnx)
            Try
                Dim dt As New DataTable
                cn.Open()
                Using da As New SqlDataAdapter(sql, cn)
                    da.Fill(dt)
                End Using
                cn.Close()
                Return dt
            Catch myException As Exception
                cn.Close()
                MsgBox("IndicadoresADO (pr_CargarIndicadorPersonal) --> " & myException.Message, MsgBoxStyle.Critical)
                Return Nothing
            End Try
        End Using
    End Function

    Public Function pr_CargarIndicadorReservas(ByVal sFiltro As String, ByVal nOpcion As Integer, ByVal sCanReg As String, ByVal lTodo As Boolean) As DataTable
        Dim sql As String = ""
        ' selecciono que grafico se quiere cargar
        Select Case nOpcion
            Case 1
                sql = String.Format("SELECT d.DesSerieId, d.DesArgumentId, SUM(d.canIndicador) as canIndicador FROM (SELECT {0} AS DesSerieId,DesMesId as DesArgumentId,canIndicador FROM DBO.V_Indicador WHERE (CodTipoIndicador='03' AND {1})) d GROUP BY d.DesSerieId,d.DesArgumentId ORDER BY d.DesSerieId, d.DesArgumentId", IIf(lTodo, "'TODO'", "DesRutaId"), sFiltro)
            Case 2
                sql = String.Format("SELECT d.DesSerieId, d.DesArgumentId, SUM(d.canIndicador) as canIndicador FROM (SELECT {0} AS DesSerieId,DesMesId as DesArgumentId,canIndicador FROM DBO.V_Indicador WHERE (CodTipoIndicador='03' AND {1})) d GROUP BY d.DesSerieId,d.DesArgumentId ORDER BY d.DesSerieId, d.DesArgumentId", IIf(lTodo, "'TODO'", "DesPasajeroId"), sFiltro)
            Case 3
                sql = String.Format("SELECT d.DesSerieId, d.DesArgumentId, SUM(d.canIndicador) as canIndicador FROM (SELECT {0} AS DesSerieId, DesMesId as DesArgumentId,canIndicador FROM DBO.V_Indicador WHERE (CodTipoIndicador='03' AND {1})) d GROUP BY d.DesSerieId,d.DesArgumentId ORDER BY d.DesSerieId, d.DesArgumentId", IIf(lTodo, "'TODO'", "NumAnioId"), sFiltro)
            Case 4
                sql = String.Format("SELECT TOP {0} DesProveedorId AS DesTerceroId,count(CanIndicador) as CanIndicador FROM DBO.V_Indicador WHERE CodTipoIndicador='03' AND DesComunidadId<>'' AND {1} GROUP BY DesProveedorId ORDER BY CanIndicador DESC", sCanReg, sFiltro)
            Case 5
                sql = String.Format("SELECT TOP {0} DesProveedorId AS DesTerceroId,count(CanIndicador) as CanIndicador FROM DBO.V_Indicador WHERE CodTipoIndicador='03' AND left(DesPasajeroId,1)<>'1' AND {1} GROUP BY DesProveedorId ORDER BY CanIndicador DESC", sCanReg, sFiltro)
            Case 6
                sql = String.Format("SELECT TOP {0} DesProveedorId AS DesTerceroId,count(CanIndicador) as CanIndicador FROM DBO.V_Indicador WHERE CodTipoIndicador='03' AND left(DesPasajeroId,1) IN ('5','6') AND {1} GROUP BY DesProveedorId ORDER BY CanIndicador DESC", sCanReg, sFiltro)
            Case 7
                sql = String.Format("SELECT TOP {0} DesProveedorId AS DesTerceroId,count(CanIndicador) as CanIndicador FROM DBO.V_Indicador WHERE CodTipoIndicador='03' AND left(DesPasajeroId,1)='7' AND {1} GROUP BY DesProveedorId ORDER BY CanIndicador DESC", sCanReg, sFiltro)
        End Select
        Using cn As New SqlConnection(MiConexion.GetCnx)
            Try
                Dim dt As New DataTable
                cn.Open()
                Using da As New SqlDataAdapter(sql, cn)
                    da.Fill(dt)
                End Using
                cn.Close()
                Return dt
            Catch myException As Exception
                cn.Close()
                MsgBox("IndicadoresADO (pr_CargarIndicadorReservas) --> " & myException.Message, MsgBoxStyle.Critical)
                Return Nothing
            End Try
        End Using
    End Function

    Public Function pr_CargarIndicadorOtros(ByVal sFiltro As String, ByVal nOpcion As Integer, ByVal sCanReg As String, ByVal lTodo As Boolean) As DataTable
        Dim sql As String = ""
        ' selecciono que grafico se quiere cargar
        Select Case nOpcion
            Case 1
                sql = String.Format("SELECT d.DesSerieId, d.DesArgumentId, SUM(d.canIndicador) as canIndicador FROM (SELECT {0} AS DesSerieId,DesMesId as DesArgumentId,canIndicador FROM DBO.V_Indicador WHERE (CodTipoIndicador='04' AND DesProveedorId='GRA1' AND {1})) d GROUP BY d.DesSerieId,d.DesArgumentId ORDER BY d.DesSerieId, d.DesArgumentId", IIf(lTodo, "'TODO'", "DesRutaId"), sFiltro)
            Case 2
                sql = String.Format("SELECT d.DesSerieId, d.DesArgumentId, SUM(d.canIndicador) as canIndicador FROM (SELECT {0} AS DesSerieId,DesMesId as DesArgumentId,canIndicador FROM DBO.V_Indicador WHERE (CodTipoIndicador='04' AND DesProveedorId='GRA2' AND {1})) d GROUP BY d.DesSerieId,d.DesArgumentId ORDER BY d.DesSerieId, d.DesArgumentId", IIf(lTodo, "'TODO'", "DesRutaId"), sFiltro)
            Case 3
                sql = String.Format("SELECT d.DesSerieId, d.DesArgumentId, SUM(d.canIndicador) as canIndicador FROM (SELECT {0} AS DesSerieId,DesMesId as DesArgumentId,canIndicador FROM DBO.V_Indicador WHERE (CodTipoIndicador='04' AND DesProveedorId='GRA3' AND {1})) d GROUP BY d.DesSerieId,d.DesArgumentId ORDER BY d.DesSerieId, d.DesArgumentId", IIf(lTodo, "'TODO'", "DesRutaId"), sFiltro)
            Case 4
                sql = String.Format("SELECT TOP {0} mv.DesPasajeroId,mv.Excepcion,mv.CanIndicador FROM (SELECT vind.DesPasajeroId,vind.Excepcion,COUNT(*) as CanIndicador FROM (SELECT '04' as codTipoIndicador,CAST(YEAR(cm.fchviaje) as char) as NumAnioId,me.desmes as DesMesId,dm.NomPasajero+' '+dm.AppPasajero+' '+dm.ApmPasajero as DesPasajeroId,Excepcion = CASE TipPorExcepcion WHEN '2' THEN 'No Embarco' WHEN '3' THEN 'Bajado del Vuelo' WHEN ' ' THEN 'No Embarco' END, rtrim(ls.NomLugar)+'-'+la.NomLugar AS DesRutaId FROM dbo.d_manifiesto dm inner join dbo.c_manifiesto cm on cm.codviaje=dm.codviaje inner join (SELECT CAST(right(anomes,2) as int) as nromes,right(anomes,2)+'-'+desmes as desmes FROM dbo.meses) me on me.nromes=MONTH(cm.fchviaje) inner join dbo.m_lugar ls on ls.codLugar=cm.codLugarOrigen inner join dbo.m_lugar la on la.codLugar=cm.codLugarDestino WHERE dm.FlgChecking=0) vind WHERE {1} GROUP BY vind.DesPasajeroId,vind.excepcion) mv ORDER BY mv.CanIndicador,mv.DesPasajeroId DESC", sCanReg, sFiltro)
        End Select
        Using cn As New SqlConnection(MiConexion.GetCnx)
            Try
                Dim dt As New DataTable
                cn.Open()
                Using da As New SqlDataAdapter(sql, cn)
                    da.Fill(dt)
                End Using
                cn.Close()
                Return dt
            Catch myException As Exception
                cn.Close()
                MsgBox("IndicadoresADO (pr_CargarIndicadorOtros) --> " & myException.Message, MsgBoxStyle.Critical)
                Return Nothing
            End Try
        End Using
    End Function

    Public Function pr_CargarIndicadorVuelos(ByVal sFiltro As String, ByVal nOpcion As Integer, ByVal sCanReg As String, ByVal lTodo As Boolean) As DataTable
        Dim sql As String = ""
        ' selecciono que grafico se quiere cargar
        Select Case nOpcion
            Case 1
                sql = String.Format("SELECT d.DesSerieId, d.DesArgumentId, SUM(d.canIndicador) as canIndicador FROM (SELECT {0} AS DesSerieId,DesMesId as DesArgumentId,canIndicador FROM DBO.V_Indicador WHERE (CodTipoIndicador='02' AND DesProveedorId='GRA1' AND {1})) d GROUP BY d.DesSerieId,d.DesArgumentId ORDER BY d.DesSerieId, d.DesArgumentId", IIf(lTodo, "'TODO'", "DesRutaId"), sFiltro)
            Case 2
                sql = String.Format("SELECT d.DesSerieId, d.DesArgumentId, SUM(d.canIndicador) as canIndicador FROM (SELECT {0} AS DesSerieId,DesMesId as DesArgumentId,canIndicador FROM DBO.V_Indicador WHERE (CodTipoIndicador='02' AND DesProveedorId='GRA2' AND {1})) d GROUP BY d.DesSerieId,d.DesArgumentId ORDER BY d.DesSerieId, d.DesArgumentId", IIf(lTodo, "'TODO'", "DesRutaId"), sFiltro)
            Case 3
                sql = String.Format("SELECT d.DesSerieId, d.DesArgumentId, SUM(d.canIndicador) as canIndicador FROM (SELECT {0} AS DesSerieId,DesMesId as DesArgumentId,canIndicador FROM DBO.V_Indicador WHERE (CodTipoIndicador='02' AND DesProveedorId='GRA3' AND {1})) d GROUP BY d.DesSerieId,d.DesArgumentId ORDER BY d.DesSerieId, d.DesArgumentId", IIf(lTodo, "'TODO'", "DesRutaId"), sFiltro)
            Case 4
                sql = String.Format("SELECT d.DesSerieId, d.DesArgumentId, SUM(d.canIndicador) as canIndicador FROM (SELECT {0} AS DesSerieId,DesMesId as DesArgumentId,canIndicador FROM DBO.V_Indicador WHERE (CodTipoIndicador='02' AND DesProveedorId='GRA4' AND {1})) d GROUP BY d.DesSerieId,d.DesArgumentId ORDER BY d.DesSerieId, d.DesArgumentId", IIf(lTodo, "'TODO'", "DesRutaId"), sFiltro)
        End Select
        Using cn As New SqlConnection(MiConexion.GetCnx)
            Try
                Dim dt As New DataTable
                cn.Open()
                Using da As New SqlDataAdapter(sql, cn)
                    da.Fill(dt)
                End Using
                cn.Close()
                Return dt
            Catch myException As Exception
                cn.Close()
                MsgBox("IndicadoresADO (pr_CargarIndicadorVuelos) --> " & myException.Message, MsgBoxStyle.Critical)
                Return Nothing
            End Try
        End Using
    End Function

    Public Function pr_EliminarIndicador(ByVal sTipo As String) As Boolean
        Dim cn As New SqlConnection(MiConexion.GetCnx)
        Using cn
            cn.Open()
            Dim oTran As SqlTransaction = cn.BeginTransaction
            Const sInstruccion As String = "DELETE FROM dbo.v_indicador WHERE (codTipoIndicador=@Tipo)"
            Try
                Dim cmd As New SqlCommand(sInstruccion, cn)
                Using cmd
                    cmd.Parameters.AddWithValue("@Tipo", sTipo.Trim)
                    cmd.Transaction = oTran
                    cmd.ExecuteNonQuery()
                End Using
                oTran.Commit()
                Return True
            Catch myException As Exception
                MsgBox("IndicadoresADO (pr_EliminarIndicador) --> " & myException.Message, MsgBoxStyle.Critical)
                oTran.Rollback()
                Return False
            End Try
        End Using
    End Function

    Public Function pr_GenerarIndicador(ByVal sTipo As String) As Boolean
        Dim cn As New SqlConnection(MiConexion.GetCnx)
        Using cn
            cn.Open()
            Dim oTran As SqlTransaction = cn.BeginTransaction
            Dim sSql As String = ""
            Select Case sTipo
                ' *- INDICADOR PERSONAL
                Case "01"
                    sSql = "insert into dbo.v_indicador (codTipoIndicador,NumAnioId,DesPasajeroId,DesMesId,DesRutaId,DesLugarSalidaId,DesLugarArriboId,DesLugarResidenciaId,DesComunidadId,DesContratanteId,DesProveedorId,CanIndicador) " & _
                           "SELECT codTipoIndicador,NumAnioId,DesPasajeroId,DesMesId,DesRutaId,DesLugarSalidaId,DesLugarArriboId,DesLugarResidenciaId,DesComunidadId,DesContratanteId,DesProveedorId,COUNT(*) AS CanIndicador FROM " & _
                           "(SELECT '01' as codTipoIndicador,CAST(YEAR(cm.fchviaje) as char) as NumAnioId,dm.NomPasajero+' '+dm.AppPasajero+' '+dm.ApmPasajero as DesPasajeroId,me.desmes as DesMesId,rtrim(ls.NomLugar)+'-'+la.NomLugar AS DesRutaId,ls.NomLugar AS DesLugarSalidaId,la.NomLugar AS DesLugarArriboId,ISNULL(lr.NomLugar, 'VARIOS') as DesLugarResidenciaId,'NO REQUIERE' as DesComunidadId,'NO REQUIERE' as DesContratanteId, 'NO REQUIERE' as DesProveedorId FROM dbo.d_manifiesto dm " & _
                           "inner join dbo.c_manifiesto cm on cm.codviaje=dm.codviaje " & _
                           "inner join dbo.v_reserva re on re.codreserva=dm.codreserva " & _
                           "inner join dbo.m_pasajero pa on re.codpasajero=pa.codpasajero " & _
                           "inner join dbo.m_lugar ls on ls.codLugar=cm.codLugarOrigen " & _
                           "inner join dbo.m_lugar la on la.codLugar=cm.codLugarDestino " & _
                           "inner join (SELECT CAST(right(anomes,2) as int) as nromes,right(anomes,2)+'-'+desmes as desmes FROM dbo.meses) me on me.nromes=MONTH(cm.fchviaje) " & _
                           "left join dbo.m_lugar lr on lr.codLugar=pa.codLugarResidencia WHERE cm.estadomanifiesto>='1') vInd " & _
                           "GROUP BY codTipoIndicador,NumAnioId,DesPasajeroId,DesMesId,DesRutaId,DesLugarSalidaId,DesLugarArriboId,DesLugarResidenciaId,DesComunidadId,DesContratanteId,DesProveedorId"
                    ' *- INDICADOR VUELOS
                Case "21"
                    sSql = "insert into dbo.v_indicador (codTipoIndicador,NumAnioId,DesPasajeroId,DesMesId,DesRutaId,DesLugarSalidaId,DesLugarArriboId,DesLugarResidenciaId,DesComunidadId,DesContratanteId,DesProveedorId,CanIndicador) " & _
                           "SELECT codTipoIndicador,NumAnioId,DesPasajeroId,DesMesId,DesRutaId,DesLugarSalidaId,DesLugarArriboId,DesLugarResidenciaId,DesComunidadId,DesContratanteId,DesProveedorId,COUNT(*) AS CanIndicador FROM " & _
                           "(SELECT '02' as codTipoIndicador,CAST(YEAR(cm.fchviaje) as char) as NumAnioId,'NO REQUIERE' as DesPasajeroId,me.desmes as DesMesId,rtrim(ls.NomLugar)+'-'+la.NomLugar AS DesRutaId,'NO REQUIERE' AS DesLugarSalidaId,'NO REQUIERE' AS DesLugarArriboId,'NO REQUIERE' as DesLugarResidenciaId,'NO REQUIERE' as DesComunidadId,'NO REQUIERE' as DesContratanteId, 'GRA1' as DesProveedorId FROM dbo.c_manifiesto cm " & _
                           "inner join dbo.m_lugar ls on ls.codLugar=cm.codLugarOrigen " & _
                           "inner join dbo.m_lugar la on la.codLugar=cm.codLugarDestino " & _
                           "inner join (SELECT CAST(right(anomes,2) as int) as nromes,right(anomes,2)+'-'+desmes as desmes FROM dbo.meses) me on me.nromes=MONTH(cm.fchviaje) " & _
                           "WHERE cm.EstadoManifiesto<>'0') vInd " & _
                           "GROUP BY codTipoIndicador,NumAnioId,DesPasajeroId,DesMesId,DesRutaId,DesLugarSalidaId,DesLugarArriboId,DesLugarResidenciaId,DesComunidadId,DesContratanteId,DesProveedorId"
                    '"WHERE cm.CodViaje in (select distinct CodViaje from dbo.D_Manifiesto)) vInd " & _
                Case "22"
                    sSql = "insert into dbo.v_indicador (codTipoIndicador,NumAnioId,DesPasajeroId,DesMesId,DesRutaId,DesLugarSalidaId,DesLugarArriboId,DesLugarResidenciaId,DesComunidadId,DesContratanteId,DesProveedorId,CanIndicador) " & _
                           "SELECT codTipoIndicador,NumAnioId,DesPasajeroId,DesMesId,DesRutaId,DesLugarSalidaId,DesLugarArriboId,DesLugarResidenciaId,DesComunidadId,DesContratanteId,DesProveedorId,COUNT(*) AS CanIndicador FROM " & _
                           "(SELECT '02' as codTipoIndicador,CAST(YEAR(pv.fchviaje) as char) as NumAnioId,'NO REQUIERE' as DesPasajeroId,me.desmes as DesMesId,rtrim(ls.NomLugar)+'-'+la.NomLugar AS DesRutaId,'NO REQUIERE' AS DesLugarSalidaId,'NO REQUIERE' AS DesLugarArriboId,'NO REQUIERE' as DesLugarResidenciaId,'NO REQUIERE' as DesComunidadId,'NO REQUIERE' as DesContratanteId, 'GRA2' as DesProveedorId FROM dbo.v_programaViaje pv " & _
                           "inner join (SELECT CAST(right(anomes,2) as int) as nromes,right(anomes,2)+'-'+desmes as desmes FROM dbo.meses) me on me.nromes=MONTH(pv.fchviaje) " & _
                           "inner join dbo.m_lugar ls on ls.codLugar=pv.codLugarOrigen " & _
                           "inner join dbo.m_lugar la on la.codLugar=pv.codLugarDestino " & _
                           "WHERE pv.codviaje in (select cm.codviaje from c_manifiesto cm left join d_manifiesto dm on cm.codviaje=dm.codviaje where dm.codviaje is null) AND pv.estadoviaje<>'9') v_indicador " & _
                           "GROUP BY codTipoIndicador,NumAnioId,DesPasajeroId,DesMesId,DesRutaId,DesLugarSalidaId,DesLugarArriboId,DesLugarResidenciaId,DesComunidadId,DesContratanteId,DesProveedorId"
                Case "23"
                    sSql = "insert into dbo.v_indicador (codTipoIndicador,NumAnioId,DesPasajeroId,DesMesId,DesRutaId,DesLugarSalidaId,DesLugarArriboId,DesLugarResidenciaId,DesComunidadId,DesContratanteId,DesProveedorId,CanIndicador) " & _
                           "SELECT codTipoIndicador,NumAnioId,DesPasajeroId,DesMesId,DesRutaId,DesLugarSalidaId,DesLugarArriboId,DesLugarResidenciaId,DesComunidadId,DesContratanteId,DesProveedorId,COUNT(*) AS CanIndicador FROM " & _
                           "(SELECT '02' as codTipoIndicador,CAST(YEAR(pv.fchviaje) as char) as NumAnioId,'NO REQUIERE' as DesPasajeroId,me.desmes as DesMesId,rtrim(ls.NomLugar)+'-'+la.NomLugar AS DesRutaId,'NO REQUIERE' AS DesLugarSalidaId,'NO REQUIERE' AS DesLugarArriboId,'NO REQUIERE' as DesLugarResidenciaId,'NO REQUIERE' as DesComunidadId,'NO REQUIERE' as DesContratanteId, 'GRA3' as DesProveedorId FROM dbo.v_programaViaje pv " & _
                           "inner join (SELECT CAST(right(anomes,2) as int) as nromes,right(anomes,2)+'-'+desmes as desmes FROM dbo.meses) me on me.nromes=MONTH(pv.fchviaje) " & _
                           "inner join dbo.m_lugar ls on ls.codLugar=pv.codLugarOrigen " & _
                           "inner join dbo.m_lugar la on la.codLugar=pv.codLugarDestino " & _
                           "WHERE pv.codviaje in (select pv.codviaje from v_programaviaje pv left join c_manifiesto cm on pv.codviaje=cm.codviaje where cm.codviaje is null) AND pv.estadoviaje<>'9') v_indicador " & _
                           "GROUP BY codTipoIndicador,NumAnioId,DesPasajeroId,DesMesId,DesRutaId,DesLugarSalidaId,DesLugarArriboId,DesLugarResidenciaId,DesComunidadId,DesContratanteId,DesProveedorId"
                Case "24"
                    sSql = "insert into dbo.v_indicador (codTipoIndicador,NumAnioId,DesPasajeroId,DesMesId,DesRutaId,DesLugarSalidaId,DesLugarArriboId,DesLugarResidenciaId,DesComunidadId,DesContratanteId,DesProveedorId,CanIndicador) " & _
                           "SELECT codTipoIndicador,NumAnioId,DesPasajeroId,DesMesId,DesRutaId,DesLugarSalidaId,DesLugarArriboId,DesLugarResidenciaId,DesComunidadId,DesContratanteId,DesProveedorId,COUNT(*) AS CanIndicador FROM " & _
                           "(SELECT '02' as codTipoIndicador,CAST(YEAR(pv.fchviaje) as char) as NumAnioId,'NO REQUIERE' as DesPasajeroId,me.desmes as DesMesId,rtrim(ls.NomLugar)+'-'+la.NomLugar AS DesRutaId,'NO REQUIERE' AS DesLugarSalidaId,'NO REQUIERE' AS DesLugarArriboId,'NO REQUIERE' as DesLugarResidenciaId,'NO REQUIERE' as DesComunidadId,'NO REQUIERE' as DesContratanteId, 'GRA4' as DesProveedorId FROM dbo.v_programaViaje pv " & _
                           "inner join (SELECT CAST(right(anomes,2) as int) as nromes,right(anomes,2)+'-'+desmes as desmes FROM dbo.meses) me on me.nromes=MONTH(pv.fchviaje) " & _
                           "inner join dbo.m_lugar ls on ls.codLugar=pv.codLugarOrigen " & _
                           "inner join dbo.m_lugar la on la.codLugar=pv.codLugarDestino " & _
                           "WHERE pv.estadoviaje='9') v_indicador " & _
                           "GROUP BY codTipoIndicador,NumAnioId,DesPasajeroId,DesMesId,DesRutaId,DesLugarSalidaId,DesLugarArriboId,DesLugarResidenciaId,DesComunidadId,DesContratanteId,DesProveedorId"
                    ' *- INDICADORES RESERVAS
                Case "03"
                    sSql = "insert into dbo.v_indicador (codTipoIndicador,NumAnioId,DesPasajeroId,DesMesId,DesRutaId,DesLugarSalidaId,DesLugarArriboId,DesLugarResidenciaId,DesComunidadId,DesContratanteId,DesProveedorId,CanIndicador) " & _
                           "SELECT codTipoIndicador,NumAnioId,DesPasajeroId,DesMesId,DesRutaId,DesLugarSalidaId,DesLugarArriboId,DesLugarResidenciaId,DesComunidadId,DesContratanteId,DesProveedorId,COUNT(*) AS CanIndicador FROM " & _
                           "(SELECT '03' as codTipoIndicador,CAST(YEAR(re.fchviaje) as char) as NumAnioId,tp.codTipoPasajero+'-'+tp.nomTipoPasajero as DesPasajeroId,me.desmes as DesMesId,er.desEstadoReserva AS DesRutaId,'NO REQUIERE' AS DesLugarSalidaId,'NO REQUIERE' AS DesLugarArriboId,'NO REQUIERE' as DesLugarResidenciaId,isnull(mc.desComunidad,'') as DesComunidadId,'NO REQUIERE' as DesContratanteId,left(an.desanl,50) as DesProveedorId FROM dbo.v_reserva re " & _
                           "inner join m_tipopasajero tp on re.tippasajero=tp.codtipopasajero " & _
                           "inner join m_estado_reserva er on re.EstadoReserva=er.CodEstadoReserva " & _
                           "inner join analisis an on re.codAnalisis=an.codanl " & _
                           "left join m_comunidad mc on re.codComunidad=mc.codComunidad " & _
                           "inner join (SELECT CAST(right(anomes,2) as int) as nromes,right(anomes,2)+'-'+desmes as desmes FROM dbo.meses) me on me.nromes=MONTH(re.fchviaje) " & _
                           "WHERE re.TipPasajero in ('1', '2','3', '4','5', '6','7', 'V')) v_indicador " & _
                           "GROUP BY codTipoIndicador,NumAnioId,DesPasajeroId,DesMesId,DesRutaId,DesLugarSalidaId,DesLugarArriboId,DesLugarResidenciaId,DesComunidadId,DesContratanteId,DesProveedorId"
                    ' *- INDICADOR OTROS
                Case "41"
                    sSql = "insert into dbo.v_indicador (codTipoIndicador,NumAnioId,DesPasajeroId,DesMesId,DesRutaId,DesLugarSalidaId,DesLugarArriboId,DesLugarResidenciaId,DesComunidadId,DesContratanteId,DesProveedorId,CanIndicador) " & _
                           "SELECT codTipoIndicador,NumAnioId,DesPasajeroId,DesMesId,DesRutaId,DesLugarSalidaId,DesLugarArriboId,DesLugarResidenciaId,DesComunidadId,DesContratanteId,DesProveedorId,SUM(CostoPasaje) AS CanIndicador FROM " & _
                           "(SELECT '04' as codTipoIndicador,CAST(YEAR(cm.fchviaje) as char) as NumAnioId,'NO REQUIERE' as DesPasajeroId,me.desmes as DesMesId,rtrim(ls.NomLugar)+'-'+la.NomLugar AS DesRutaId,ls.NomLugar AS DesLugarSalidaId,la.NomLugar AS DesLugarArriboId,'NO REQUIERE' as DesLugarResidenciaId,'NO REQUIERE' as DesComunidadId,'NO REQUIERE' as DesContratanteId, 'GRA1' as DesProveedorId,ISNULL(dm.MonPasaje, ru.PrePasaje) as CostoPasaje FROM dbo.d_manifiesto dm " & _
                           "INNER JOIN dbo.c_manifiesto cm ON dm.codviaje=cm.codviaje " & _
                           "INNER JOIN dbo.v_reserva re ON dm.codreserva=re.codreserva " & _
                           "INNER JOIN dbo.m_lugar ls on ls.codLugar=cm.codLugarOrigen " & _
                           "INNER JOIN dbo.m_lugar la on la.codLugar=cm.codLugarDestino " & _
                           "INNER JOIN dbo.m_rutas ru ON cm.CodLugarOrigen=ru.codOrigen and cm.CodLugarDestino=ru.CodDestino " & _
                           "INNER JOIN (SELECT CAST(right(anomes,2) as int) as nromes,right(anomes,2)+'-'+desmes as desmes FROM dbo.meses) me on me.nromes=MONTH(re.fchviaje) " & _
                           "WHERE re.flgAsumemarsa=1 AND cm.estadomanifiesto>='1') v_indicador " & _
                           "GROUP BY codTipoIndicador,NumAnioId,DesPasajeroId,DesMesId,DesRutaId,DesLugarSalidaId,DesLugarArriboId,DesLugarResidenciaId,DesComunidadId,DesContratanteId,DesProveedorId"
                Case "42"
                    sSql = "insert into dbo.v_indicador (codTipoIndicador,NumAnioId,DesPasajeroId,DesMesId,DesRutaId,DesLugarSalidaId,DesLugarArriboId,DesLugarResidenciaId,DesComunidadId,DesContratanteId,DesProveedorId,CanIndicador) " & _
                           "SELECT codTipoIndicador,NumAnioId,DesPasajeroId,DesMesId,DesRutaId,DesLugarSalidaId,DesLugarArriboId,DesLugarResidenciaId,DesComunidadId,DesContratanteId,DesProveedorId,SUM(AsientosDisponibles) AS CanIndicador FROM " & _
                           "(SELECT '04' as codTipoIndicador,CAST(YEAR(pv.fchviaje) as char) as NumAnioId,'NO REQUIERE' as DesPasajeroId,me.desmes as DesMesId,rtrim(ls.NomLugar)+'-'+la.NomLugar AS DesRutaId,ls.NomLugar AS DesLugarSalidaId,la.NomLugar AS DesLugarArriboId,'NO REQUIERE' as DesLugarResidenciaId,'NO REQUIERE' as DesComunidadId,'NO REQUIERE' as DesContratanteId, 'GRA2' as DesProveedorId,pv.nroCupos-dm.CanPas as AsientosDisponibles FROM dbo.v_ProgramaViaje pv " & _
                           "inner join (select codViaje,count(*) as CanPas from d_manifiesto group by codviaje) dm on pv.codViaje=dm.codViaje " & _
                           "inner join dbo.m_lugar ls on ls.codLugar=pv.codLugarOrigen " & _
                           "inner join dbo.m_lugar la on la.codLugar=pv.codLugarDestino " & _
                           "inner join (SELECT CAST(right(anomes,2) as int) as nromes,right(anomes,2)+'-'+desmes as desmes FROM dbo.meses) me on me.nromes=MONTH(pv.fchviaje) where pv.EstadoViaje<>'9' ) vInd " & _
                           "GROUP BY codTipoIndicador,NumAnioId,DesPasajeroId,DesMesId,DesRutaId,DesLugarSalidaId,DesLugarArriboId,DesLugarResidenciaId,DesComunidadId,DesContratanteId,DesProveedorId"
                Case "43"
                    sSql = "insert into dbo.v_indicador (codTipoIndicador,NumAnioId,DesPasajeroId,DesMesId,DesRutaId,DesLugarSalidaId,DesLugarArriboId,DesLugarResidenciaId,DesComunidadId,DesContratanteId,DesProveedorId,CanIndicador) " & _
                           "SELECT codTipoIndicador,NumAnioId,DesPasajeroId,DesMesId,DesRutaId,DesLugarSalidaId,DesLugarArriboId,DesLugarResidenciaId,DesComunidadId,DesContratanteId,DesProveedorId,COUNT(*) AS CanIndicador FROM " & _
                           "(SELECT '04' AS codTipoIndicador,CAST(YEAR(cm.fchviaje) AS CHAR) AS NumAnioId,'NO REQUIERE' AS DesPasajeroId,me.desmes AS DesMesId,rtrim(ls.NomLugar)+'-'+la.NomLugar AS DesRutaId,ls.NomLugar AS DesLugarSalidaId,la.NomLugar AS DesLugarArriboId,'NO REQUIERE' AS DesLugarResidenciaId,'NO REQUIERE' AS DesComunidadId,'NO REQUIERE' AS DesContratanteId, 'GRA3' AS DesProveedorId FROM dbo.c_manifiesto cm " & _
                           "INNER JOIN dbo.m_lugar ls on ls.codLugar=cm.codLugarOrigen " & _
                           "INNER JOIN dbo.m_lugar la on la.codLugar=cm.codLugarDestino " & _
                           "INNER JOIN (SELECT CAST(right(anomes,2) AS INT) AS nromes,right(anomes,2)+'-'+desmes AS desmes FROM dbo.meses) me ON me.nromes=MONTH(cm.fchviaje) " & _
                           "WHERE cm.estadomanifiesto>='2' AND codviaje IN (SELECT DISTINCT codviaje FROM dbo.d_manifiesto WHERE FlgChecking=0)) v_indicador " & _
                           "GROUP BY codTipoIndicador,NumAnioId,DesPasajeroId,DesMesId,DesRutaId,DesLugarSalidaId,DesLugarArriboId,DesLugarResidenciaId,DesComunidadId,DesContratanteId,DesProveedorId"
            End Select
            Try
                Dim cmd As New SqlCommand(sSql, cn)
                Using cmd
                    cmd.Transaction = oTran
                    cmd.ExecuteNonQuery()
                End Using
                oTran.Commit()
                Return True
            Catch myException As Exception
                MsgBox("IndicadoresADO (pr_GenerarIndicador) --> " & myException.Message, MsgBoxStyle.Critical)
                oTran.Rollback()
                Return False
            End Try
        End Using
    End Function

End Class